/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db.definitions;

import java.util.ArrayList;
import java.util.Iterator;

/**
 *
 * @author abracadabuda
 */
public class TableDefinition extends AbstractDefinition {

    private String tblName;
    private String name;
    private String schema;
    private String comment;
    private String catalog;
    private DatabaseDefinition database;
    private ArrayList<ReferenceDefinition> references = new ArrayList<ReferenceDefinition>();
    private ArrayList<ColumnDefinition> columns = new ArrayList<ColumnDefinition>();
    private ArrayList<String> primaryKeys = new ArrayList<String>();
    private ArrayList<IndexDefinition> indexes = new ArrayList<IndexDefinition>();
    private IndexDefinition primaryKey;

    public TableDefinition(DatabaseDefinition database) {
        this.database = database;
        schema = AbstractDefinition.prefix;
    }

    public String getTblName() {
        return tblName;
    }

    public void setTblName(String tblName) {
        this.tblName = tblName;
    }

    public String getSchema() {
        return schema;
    }

    public void setSchema(String schema) {
        this.schema = schema;
    }

    public String getComment() {
        return comment;
    }

    public void setComment(String comment) {
        this.comment = comment;
    }

    public String getCatalog() {
        return catalog;
    }

    public void setCatalog(String catalog) {
        this.catalog = catalog;
    }

    public ArrayList<ColumnDefinition> getColumns() {
        return columns;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public ArrayList<String> getPrimaryKeys() {
        return primaryKeys;
    }

    public void addColumn(ColumnDefinition column) {
        columns.add(column);
        if (column.isPk()) {
            primaryKeys.add(column.getName());
        }
    }

    public void addReference(ReferenceDefinition reference) {
        references.add(reference);
    }

    /**
     * prints dump of table definition (for testing)
     */
    public void getDump() {
        System.out.println("\tTable:");
        System.out.println("\t\tName: " + name);
        System.out.println("\t\tCatalog: " + catalog);
        System.out.println("\t\tSchema: " + schema);
        System.out.println("\t\tTbl-name: " + tblName);
        System.out.println("\t\tComment: " + comment);
        for (Iterator i = columns.iterator(); i.hasNext();) {
            ColumnDefinition column = (ColumnDefinition) i.next();
            column.getDump();
        }

        for (Iterator i = indexes.iterator(); i.hasNext();) {
            IndexDefinition index = (IndexDefinition) i.next();
            index.getDump();
        }
    }

    /**
     * returns SQL code fore creating table (CREATE TALBE...)
     * @return SQL code
     */
    public String getSQL() {
        orderColumns();
        String sql = "CREATE TABLE ";
        //sql += schema.equals("") ? "" : "\"" + schema + "\".";
        sql += "\"" + schema + "\".";
        sql += "\"" + tblName + "\" (\n";

        for (Iterator i = columns.iterator(); i.hasNext();) {
            ColumnDefinition column = (ColumnDefinition) i.next();
            sql += column.getColumnForCreateTableSQL();
            if (i.hasNext()) {
                sql += ",\n";
            }
        }
        sql += ",\n " + primaryKey.getPrimaryKeySQL() + "\n" + ")";
        return sql + ";\n";
    }

    /**
     * returns SQL code for all indexes on table
     * @return SQL code
     */
    
    public String getIndexesSQL() {
        String sql = "";
        for (Iterator i = indexes.iterator(); i.hasNext();) {
            IndexDefinition index = (IndexDefinition) i.next();
            if (!index.getSQL().equals("")) {
                sql += index.getSQL();
            }
        }
        return sql;
    }

    /**
     * returns SQL cde for all column comments on table columns
     * @return SQL code
     */
    public String getColumnsCommentsSQL() {
        String sql = "";
        for (Iterator i = columns.iterator(); i.hasNext();) {
            ColumnDefinition column = (ColumnDefinition) i.next();
            if (!column.getCommentSQL().equals("")) {
                sql += column.getCommentSQL();
            }
        }
        return sql;
    }

    /**
     * returns SQL code for comment on table
     * @return SQL code
     */
    public String getCommentSQL() {
        if (comment.equals("")) {
            return "";
        }
        String sql = "COMMENT ON TABLE ";
        sql += "\"" + schema + "\".\"";
        sql += tblName + "\" ";
        sql += "IS '" + comment + "';\n";
        return sql;
    }

    public DatabaseDefinition getDatabase() {
        return database;
    }

    public ArrayList<IndexDefinition> getIndexes() {
        return indexes;
    }

    public void addIndex(IndexDefinition index) {
        indexes.add(index);
        if (index.isPk()) {
            primaryKey = index;
        }
    }

    public ArrayList<ReferenceDefinition> getReferences() {
        return references;
    }

    /**
     * returns list of references by given name of reference (usally returns 1 reference)
     * @param name name of reference
     * @return list of references
     */
    public ArrayList<ReferenceDefinition> getReferencesByName(String name) {
        ArrayList<ReferenceDefinition> referencesList = new ArrayList<ReferenceDefinition>();
        for (Iterator i = references.iterator(); i.hasNext();) {
            ReferenceDefinition reference = (ReferenceDefinition) i.next();
            if (reference.getName().equals(name)) {
                referencesList.add(reference);
            }
        }

        return referencesList;
    }

    /**
     * returns SQL code for all references on table
     * @return SQL code
     */
    public String getReferencesSQL() {
        String sql = "";

        for (Iterator i = getNamesOfReferences().iterator(); i.hasNext();) {
            String refName = (String) i.next();
            String fkCols = "";
            String localCols = "";
            ReferenceDefinition reference = null;
            for (Iterator ref = getReferencesByName(refName).iterator(); ref.hasNext();) {
                ReferenceDefinition refByName = (ReferenceDefinition) ref.next();
                if (!fkCols.equals("")) {
                    fkCols += ", ";
                    localCols += ", ";
                }
                fkCols += "\"" + refByName.getColumn().getName() + "\"";
                localCols += "\"" + refByName.getColName() + "\"";
                reference = refByName;
            }
            sql += "ALTER TABLE \"";
            sql += schema + "\".";
            sql += "\"" + tblName + "\" ";
            sql += "ADD CONSTRAINT \"" + refName + "\" FOREIGN KEY (";
            sql += fkCols + ") \n";
            sql += "REFERENCES ";
            sql += "\"" + reference.getSchema() + "\".";
            sql += "\"" + reference.getTblName() + "\"(" + localCols + ")\n";
            sql += "MATCH SIMPLE\n";
            sql += "ON DELETE " + reference.getDeleteRule() + "\n";
            sql += "ON UPDATE " + reference.getUpdateRule() + "\n";
            sql += reference.getDefferable() + ";\n";
        }
        return sql;
    }

    /**
     * returns list of references on table
     * References in XML are not stored as one for all columns in reference, by every column has attribute (and name of reference) that it is referenced or not.
     * Therefor for unique reference names, all columns must be searched.
     * @return
     */
    private ArrayList<String> getNamesOfReferences() {
        ArrayList<String> strings = new ArrayList<String>();
        for (Iterator i = references.iterator(); i.hasNext();) {
            ReferenceDefinition reference = (ReferenceDefinition) i.next();
            boolean existuje = false;
            for (Iterator j = strings.iterator(); j.hasNext();) {
                String str = (String) j.next();
                if (str.equals(reference.getName())) {
                    existuje = true;
                }
            }
            if (!existuje) {
                strings.add(reference.getName());
            }
        }
        return strings;
    }

    /*
     * return columns ordered by column.position
     */
    private void orderColumns() {
        ArrayList<ColumnDefinition> columnsOrdered = new ArrayList<ColumnDefinition>();
        for (int i = 0; i < columns.size(); i++) {
            for (Iterator colIter = columns.iterator(); colIter.hasNext();) {
                ColumnDefinition column = (ColumnDefinition) colIter.next();
                if (column.getPosition() == i + 1) {
                    columnsOrdered.add(i, column);
                }
            }
        }
        columns = columnsOrdered;
    }

    public IndexDefinition getPrimaryKey() {
        return primaryKey;
    }

    public void setPrimaryKey(IndexDefinition primaryKey) {
        this.primaryKey = primaryKey;
    }
}
